/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package CRUD;

import Beans.BeanPlanoSaude;
import Conexao.ConexaoBanco;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Marcos
 */
public class CRUDPlanoSaude {
    
    public Connection conexao;

    public CRUDPlanoSaude() throws SQLException {
        this.conexao = ConexaoBanco.getConexao();
    }
    
    public void adiciona(BeanPlanoSaude BPS) throws SQLException {
        String sql = "insert into plano_saude(den_plano_saude)" + "values(?)";
        PreparedStatement stmt = conexao.prepareStatement(sql);                
        stmt.setString(1, BPS.getDen_plano_saude());                             
        stmt.execute();
        stmt.close();
    }
    
    public void altera(BeanPlanoSaude BPS) throws SQLException {
        String sql = "update plano_saude set den_plano_saude = ?"
                + " WHERE cod_plano_saude = ? ";
        PreparedStatement stmt = conexao.prepareStatement(sql);
        stmt.setString(1, BPS.getDen_plano_saude());                 
        stmt.setInt(2, BPS.getCod_plano_saude());     
        stmt.execute();
        stmt.close();
    }
    
    public void remove(BeanPlanoSaude BPS) throws SQLException {
        String sql = "delete from plano_saude where cod_plano_saude = ? ";
        PreparedStatement stmt = conexao.prepareStatement(sql);
        stmt.setInt(1, BPS.getCod_plano_saude());
        stmt.execute();
        stmt.close();
    }
    
    public List<BeanPlanoSaude> getListaPlanoSaude() throws SQLException {
        String sql = "select * from plano_saude";       

        PreparedStatement stmt = this.conexao.prepareStatement(sql);               
        ResultSet rs = stmt.executeQuery();
        List<BeanPlanoSaude> ListaPlanoSaude = new ArrayList<BeanPlanoSaude>();
        while (rs.next()) {
            BeanPlanoSaude BPS = new BeanPlanoSaude();
            BPS.setCod_plano_saude(rs.getInt("cod_plano_saude"));
            BPS.setDen_plano_saude(rs.getString("den_plano_saude"));                        
            ListaPlanoSaude.add(BPS);
        }
        rs.close();
        stmt.close();
        return ListaPlanoSaude;
    }
    
    public List<BeanPlanoSaude> getListaPlanoSaudeCod(String den_plano_saude) throws SQLException {
        String sql = "select * from plano_saude where den_plano_saude = ?";       

        PreparedStatement stmt = this.conexao.prepareStatement(sql);
        stmt.setString(1, den_plano_saude);        
        ResultSet rs = stmt.executeQuery();
        List<BeanPlanoSaude> ListaPlanoSaude = new ArrayList<BeanPlanoSaude>();
        while (rs.next()) {
            BeanPlanoSaude BPS = new BeanPlanoSaude();
            BPS.setCod_plano_saude(rs.getInt("cod_plano_saude"));
            BPS.setDen_plano_saude(rs.getString("den_plano_saude"));                        
            ListaPlanoSaude.add(BPS);
        }
        rs.close();
        stmt.close();
        return ListaPlanoSaude;
    }
    
    public List<BeanPlanoSaude> getListaPlanoSaudeDen(String den_plano_saude) throws SQLException {
        String sql = "select * from plano_saude where den_plano_saude like ?";       

        PreparedStatement stmt = this.conexao.prepareStatement(sql);
        stmt.setString(1, den_plano_saude);        
        ResultSet rs = stmt.executeQuery();
        List<BeanPlanoSaude> ListaPlanoSaude = new ArrayList<BeanPlanoSaude>();
        while (rs.next()) {
            BeanPlanoSaude BPS = new BeanPlanoSaude();
            BPS.setCod_plano_saude(rs.getInt("cod_plano_saude"));
            BPS.setDen_plano_saude(rs.getString("den_plano_saude"));                        
            ListaPlanoSaude.add(BPS);
        }
        rs.close();
        stmt.close();
        return ListaPlanoSaude;
    }
}